use master
create database bbs
on
(
	name='bbs',
	filename='D:\Demo\bbs.mdf',
	size=5mb,
	maxsize=50mb,
	filegrowth=10%
)
log on
(
	name='bbs_log',
	filename='D:\Demo\bbs_log.ldf',
	size=5mb,
	maxsize=50mb,
	filegrowth=10%
)
go
use bbs
create table bbsUser
(
	uID int identity(1,1) not null,
	uName varchar(10)  not null,
	uSex  varchar(2) not null,
	uAge  int not null,
	uPoint  int not null
)

create table bbsSection
(
	sID  int identity(1,1) not null , 
	sName  varchar(10) not null,
	sUid   int,
)

alter table bbsUser add constraint PK_bbsUser_uID primary key(uID)
alter table bbsUser add constraint UK_bbsUser_uName unique(uName)
alter table bbsUser add constraint CK_bbsUser_uSex check(uSex='男' or uSex='女')
alter table bbsUser add constraint CK_bbsUser_uAge check(uAge>=15 and uAge<=60)
alter table bbsUser add constraint CK_bbsUser_uPoint check(uPoint>=0)

alter table bbsSection add constraint PK_bbsSection_sID primary key(sID)
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUid) references bbsUser(uID)


create table bbsTopic
(
	tID int primary key identity(1,1),
	tUID int references bbsUser(uID),
	tSID int references bbsSection(sID),
	tTitle varchar(100) not null,
	tMsg text not null,
	tTime datetime,
	tCount int
)
create table bbsReply
(
	 rID  int identity(1,1) primary key,
	 rUID  int references  bbsUser(uID),
	 rTID  int references bbsTopic(tID),
	 rMsg text not null,
	 rTime datetime
)

insert into bbsUser (uName,uSex,uAge,uPoint) values ('小雨点','女','20','0')
,('逍遥','男','18','4'),('七年级生','男','19','2')

select uName,uPoint into bbsPoint from bbsUser

insert into bbsSection (sName,sUid) values ('技术交流','1'),
('读书世界','3'),('生活百科','1'),('八卦区','3')

insert into bbsTopic (tUID,tSID,tTitle,tMsg,tTime,tCount) values ('2','4','范跑跑','谁是范跑跑','2008-7-8','1'),
('3','1','.NET','谁是范跑跑','2008-9-1','2'),('1','3','.今年夏天最流行什么呀?','有谁知道今年夏天最流行','2008-9-10','0')

i

--1.在主贴表中统计每个版块的发帖总数
  select tSID as 板块编号 , count(tTitle) as 发帖总数 from bbsTopic group by tSID
--2.在回帖表中统计每个主贴的回帖总数量
  select  rID as 回帖编号, count(rTID) as 回帖总数量 from bbsReply group by rID
--3.在主贴表中统计每个用户的发的主帖的总数
  select tUID as 发帖编号, count(tID) as 主贴总数 from bbsTopic group by tUID
--4.在主贴表中统计每个用户发的主贴的回复数量总和
  select tUID as 用户编号, sum(tCount) from bbsTopic group by tUID
--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量
  select tSID as 板块编号, AVG(tID) from bbsTopic group by tSID having AVG(tID)>3
--6.在用户信息表中查询出积分最高的用户的用户名，性别，年龄和积分
  select top 1* from bbsUser order by uPoint desc  --排序

  select MAX(uPoint) from bbsUser --子查询

 select * from bbsUser where uPoint=(select MAX(uPoint) from bbsUser)

--7.在主贴表中（bbsTopic）中将帖子的内容或标题中有“快乐”两字的记录查询出来
  select*from bbsTopic where  tTitle like '%快乐%' or  tMsg like '%快乐%'
--8.在用户信息表（bbsUsers）中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来（用多种方法实现）
  select * from bbsUser where ((uAge between 15 and 20)and uPoint>10)
  select * from bbsUser where (uAge >= 15 and uAge <= 20) and  uPoint>10
--9.在用户信息表（bbsUsers）中将用户名的第一个字为“小”，第三字为“大”的用户信息查询出来
  select * from bbsUser where uName like ('小') and  uName like ('__大')
--10.在主贴表（bbsTopic）中将在2008-9-10 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来，并且为列取上对应的中文列名
  select  tTitle 标题, tMsg 内容 from bbsTopic where tTime>' 2008-9-10 12:00:00 ' and tCount>10                                                                                        
--11.在主贴表（bbsTopic）中将帖子的标题是以‘！’结尾的帖子的发帖人编号和回复数量查询出来`
  select tID,tCount,tTitle from bbsTopic where tTitle like '！'